package com.zhike.b2b.mall.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.zhike.b2b.base.BaseService;
import com.zhike.b2b.mall.IGoodsService;
import com.zhike.b2b.model.ClassVo;
import com.zhike.b2b.model.ProductClassVo;
import com.zhike.b2b.model.ProductVo;
import com.zhike.b2b.product.IProductService;
import com.zhike.b2b.util.ClassUtil;
import com.zhike.b2b.util.StringUtil;

@Service
public class GoodsService extends BaseService implements IGoodsService {

	/* (non-Javadoc)
	 * @see com.zhike.b2b.mall.impl.IGoodsService#querySpecProds(java.lang.String, int, int)
	 */
	public List<ProductVo> querySpecProds(String clzId,int begin,int num)
	{
		final String defaultLogoImg = ps.getParamValue("default_img");
		String sql = "select file_path,product_price,moneytype,product_id,product_name ,class_id from maxproduct m left join attach a on m.attach_id = a.attach_root_id where m.display_start_date <=now() and m.display_end_date >=now() and m.display=1 and m.validity =1 and m.class_id like ? limit ?,? ";
		List<ProductVo> lpcv = jt.query(sql,  new RowMapper<ProductVo>() {
			 public ProductVo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
				 ProductVo pv = new ProductVo();
				 pv.setClassId(rs.getString("class_id"));
				 pv.setProductId(rs.getString("product_id"));
				 pv.setProductName(rs.getString("product_name"));
				 pv.setPrice(rs.getDouble("product_price"));
				 pv.setMoneytype(rs.getString("moneytype"));
				 //pv.setUrl(ClassUtil.getObjUrl(pv, pv.getProductId()));
				 String smallImg = rs.getString("file_path");
				if(smallImg == null || smallImg.trim().length()<1)
					smallImg = defaultLogoImg;
				pv.setSmallImg(smallImg);
				return pv;
			}},clzId+"%",begin,num);
		return lpcv;
	}
	/**
	 * 获得商城商品类别
	 * @param start
	 * @param size
	 * @return
	 */
	public List<ProductClassVo> getGoodsClass(String type)
	  {
	    String sql ;
	    if (type.equals("kongzhi"))
	    {
	    	sql = "select * from (select  count(b.class_id) as datacount,bc.class_id,bc.class_name, bc.store_path,bc.home_name,bc.list_rule from product_class bc left join maxproduct b  on b.class_id  like CONCAT(bc.class_id,'%') and b.display_start_date <=now() and b.display_end_date >=now() and b.display=1 and b.validity =1 where bc.checked=1 and  bc.remove_tag=1 and bc.class_id like '100101%' group by bc.class_id) b where length(b.class_id)=10";
	    }
	    else if (type.equals("all"))
	    {
	    	sql = "select * from (select  count(b.class_id) as datacount,bc.class_id,bc.class_name, bc.store_path,bc.home_name,bc.list_rule from product_class bc left join maxproduct b  on b.class_id  like CONCAT(bc.class_id,'%') and b.display_start_date <=now() and b.display_end_date >=now() and b.display=1 and b.validity =1 where bc.checked=1 and  bc.remove_tag=1 and bc.class_id like '100%' group by bc.class_id) b where length(b.class_id)=6";
	    }
	    else if (type.equals("sheshi"))
	    {
	    	sql = "select * from (select  count(b.class_id) as datacount,bc.class_id,bc.class_name, bc.store_path,bc.home_name,bc.list_rule from buying_class bc left join buying b  on b.class_id  like CONCAT(bc.class_id,'%') and b.start_date <=now() and b.end_date >=now() and b.display=1 and b.validity =1 where bc.checked=1 and  bc.remove_tag=1 and bc.class_id like '100100%' group by bc.class_id) b where length(b.class_id)=10";
	    }else
	    {
	      String str = "";
	      if (type.length() == 0)
	        str = "3";
	      if (type.length() == 3)
	        str = "6";
	      if (type.length() == 6)
	        str = "6";
	      if (type.length() == 10)
	        str = "10";
	      if (type.length() == 15)
	        str = "15";
	      sql = "select * from (select  count(b.class_id) as datacount,bc.class_id,bc.class_name, bc.store_path,bc.home_name,bc.list_rule from product_class bc left join maxproduct b  on b.class_id  like CONCAT(bc.class_id,'%') and b.display_start_date <=now() and b.display_end_date >=now() and b.display=1 and b.validity =1 where bc.checked=1 and  bc.remove_tag=1 and bc.class_id like '" + type + "%' " + "group by bc.class_id) b where length(b.class_id)='" + str + "'";
	    }
	    List<ProductClassVo> lpcs = jt.query(sql,  new RowMapper<ProductClassVo>() {
			 public ProductClassVo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
				 ProductClassVo pcv = new ProductClassVo();
				 pcv.setClassId(rs.getString("class_id"));
				 pcv.setListRule(rs.getString("list_rule"));
				 pcv.setStorePath(rs.getString("store_path"));
				 pcv.setClassName(rs.getString("class_name"));
				 pcv.setHomeName(rs.getString("home_name"));
				 pcv.setDataCount(rs.getInt("datacount"));
				 pcv.setListUrl(ClassUtil.getListUrl(pcv, 1));
				 return pcv;
			}
		});
	    return lpcs;
	  }
	
	  /**
	   * 按首字母获取所有产品(供应)类别列表
	   * @param paramString1
	   * @param paramString2
	   * @return
	   */
	  public Map<String,List<ProductClassVo>> getClassNameByChar()
	  {
		List<ProductClassVo> allPcs = getAllProdClass();
		Map<String,List<ProductClassVo>> mslp = new HashMap<String,List<ProductClassVo>>();

	    for(char chr = 'a';chr<='z';chr++){
	    	List<ProductClassVo> lpc = new ArrayList<ProductClassVo>();
			 for (ProductClassVo pcv : allPcs)
			 {
			     String f = StringUtil.getFirstStr(pcv.getClassName());
			     if(f.toLowerCase().equals(chr+""))
			     {
			    	 lpc.add(pcv) ;
			     }
			 }
			 mslp.put(chr+"", lpc);
		}
	    return mslp;
	  }
	/**
	 * 根据条件查询产品(供应)列表
	 * 
	 * @param classId
	 * @param limitStr
	 * @param orderCln
	 * @param commend
	 * @return
	 */
	public List<ProductVo> queryProducts(String classId,String commend ,String limitStr,
			String orderCln) {
		final String defaultLogoImg = ps.getParamValue("default_img");
       StringBuffer sql = new StringBuffer("select distinct p.product_id,p.product_name,p.attach_id,a.file_path prod_photo,p.class_id,p.small_img,p.big_img,pc.list_rule,pc.store_path,pc.article_rule,p.commend,p.good_product,p.list_top,p.num ");
	   
       sql.append(" from product p  left join attach a on p.attach_id = a.attach_root_id inner join product_class pc on pc.class_id=p.class_id where p.display_start_date <=now() and p.display_end_date >=now() and p.display=1 and p.validity =1 ");
	    if (!classId.equals(""))
	    	sql.append(" and p.class_id like '" + classId + "%'");
	    if (!commend.equals(""))
	    	sql.append(" and p.commend=" + commend);
	    if (orderCln.equals(""))
	        sql.append("  order by p." +orderCln +" desc");
	    if (!limitStr.equals(""))
	    	sql.append(" limit " + limitStr );
		List<ProductVo> lnc = jt.query(sql.toString(), new RowMapper<ProductVo>() {
			public ProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ProductVo pv = new ProductVo();
				pv.setClassId(rs.getString("class_id"));
				pv.setProductId(rs.getString("product_id"));
				pv.setListRule(rs.getString("list_rule"));
				pv.setStorePath(rs.getString("store_path"));
				pv.setArticleRule(rs.getString("article_rule"));
				pv.setProductName(rs.getString("product_name"));
				String smallImg = rs.getString("prod_photo");
				if(smallImg == null || smallImg.trim().length()<1)
					smallImg = defaultLogoImg;
				pv.setSmallImg(smallImg);
				
				pv.setCommend(rs.getString("commend"));
				pv.setBigImg(rs.getString("big_img"));
				pv.setNum(rs.getString("num"));
                pv.setListTop(rs.getString("list_top"));
				pv.setUrl(ClassUtil.getObjUrl(pv, pv.getProductId()));
				return pv;
			}
		});
		return lnc;
	}

	
	  public String getProdSqlstr(String orderStr, String limitStr, String commend, String classId, String custId, String isHot, String isGood, String levelId, String type, String custType)
	  {
	    StringBuffer sql = new StringBuffer("select distinct p.product_id,p.product_name,p.attach_id,p.class_id,p.small_img,p.big_img,p.commend,p.good_product,p.list_top,p.num,ai.area_name ");
	    
	    StringBuffer subSql = new StringBuffer(" from(select p.attach_id,p.product_id,p.cust_id,p.area_id,p.product_name,p.class_id,p.small_img,p.big_img,p.commend,p.good_product,p.list_top,p.num,p.publish_user_id");
	   
	    subSql.append(" from product p inner join product_class pc on pc.class_id=p.class_id where p.display_start_date <=now() and p.display_end_date >=now() and p.display=1 and p.validity =1 ");
	    if (!classId.equals(""))
	      subSql.append(" and p.class_id like '" + classId + "%'");
	    if (!commend.equals(""))
	      subSql.append(" and p.commend=" + commend);
	    
	    if (!isHot.equals(""))
	      subSql.append(" and p.hot_product=" + isHot);
	    if (!isGood.equals(""))
	      subSql.append(" and p.good_product=" + isGood);
	    if (!custId.equals(""))
	      subSql.append(" and p.cust_id=" + custId);
	    if (orderStr.equals(""))
	    {
	      subSql.append("  order by p.commend  desc  ,p.good_product desc,p.hot_product desc ,p.list_top desc,p.publish_date desc ");
	    }else
	    {
	      
	    }
	    if (!limitStr.equals(""))
	      subSql.append(" limit " + limitStr);
	    sql.append(subSql);
	    sql.append(" ) p inner join company c on c.cust_id=p.cust_id ");
	    if (!levelId.equals(""))
	      sql.append(" and c.level_id=" + levelId);
	    if (!type.equals(""))
	      sql.append(" and c.type=" + type);
	    if (!custType.equals(""))
	      sql.append(" and c.cust_type=" + custType);
	    sql.append(" inner join user_info u on p.publish_user_id=u.user_id left join area_info ai on substr(p.area_id,1,6)=ai.area_id ");
	    logger.debug("当前product查询的语句为：" + sql.toString());
	    return sql.toString();
	  }
	
	private List<ProductClassVo> getAllProdClass()
	{
		String sql = "  select bc.class_id,bc.class_name,bc.store_path,bc.home_name,bc.list_rule from product_class bc where bc.remove_tag = 1 ";
		List<ProductClassVo> lpcs = jt.query(sql,  new RowMapper<ProductClassVo>() {
			 public ProductClassVo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
				 ProductClassVo pcv = new ProductClassVo();
				 pcv.setClassId(rs.getString("class_id"));
				 pcv.setListRule(rs.getString("list_rule"));
				 pcv.setStorePath(rs.getString("store_path"));
				 pcv.setClassName(rs.getString("class_name"));
				 pcv.setListUrl(ClassUtil.getListUrl(pcv, 1));
				 return pcv;
			}
			 });
		 return lpcs;
	}
	
	/**
	 * 获取推荐商品(供应)列表
	 * @param begin
	 * @param num
	 * @return
	 */
	public  List<ProductVo> getRecommendProds(int begin,int num)
	{
		final String defaultLogoImg = ps.getParamValue("default_img");
		String sql = "select  file_path,product_id,product_name ,class_id FROM ( SELECT product_id, product_name, class_id , m.attach_id as attach_id "
    		  +" FROM maxproduct m where  m.display_start_date <= now() AND m.display_end_date >= now() AND m.display = 1 AND m.validity = 1 AND 	m.commend = '1' ORDER BY oper_time DESC  LIMIT " +begin+","+num+
    		  		" ) m LEFT JOIN attach a ON m.attach_id = a.attach_root_id ";
		List<ProductVo> lnc = jt.query(sql.toString(), new RowMapper<ProductVo>() {
			public ProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ProductVo pv = new ProductVo();
				pv.setClassId(rs.getString("class_id"));
				pv.setProductId(rs.getString("product_id"));
				//pv.setListRule(rs.getString("list_rule"));
				//pv.setStorePath(rs.getString("store_path"));
				//pv.setArticleRule(rs.getString("article_rule"));
				pv.setProductName(rs.getString("product_name"));
				//pv.setPublishTime(rs.getString("publish_date"));
				//pv.setUrl(ClassUtil.getObjUrl(pv, pv.getProductId()));
				String smallImg = rs.getString("file_path");
				if(smallImg == null || smallImg.trim().length()<1)
					smallImg = defaultLogoImg;
				pv.setSmallImg(smallImg);
				return pv;
			}
		});
		return lnc;
	}
}
